﻿using Mapster;
using Microsoft.AspNetCore.Mvc;
using RuoVea.DynamicWebApi;
using RuoVea.ExDto;
using RuoVea.ExEnum;
using RuoVea.ExIdGen;
using RuoVea.ExSugar;
using RuoVea.ExSugar.Entity;
using RuoVea.ExSugar.UnitOfWork;
using RuoVea.ExUtil;
using RuoVea.ExUtil.Exceptions;
using RuoVea.OmiApi.Reports.Dto;
using RuoVea.OmiApi.Reports.Language;
using RuoVea.OmiApi.Reports.Models;
using RuoVea.OmiApi.Reports.Util;
using SqlSugar;
using System.Data;

namespace RuoVea.OmiApi.Reports.Service;

/// <summary>
/// 脚本管理
/// </summary>
[ApiExplorerSettings(GroupName = "Report")]
public partial class RptSqlService : IApplicationService
{
    /// <summary>
    /// 
    /// </summary>
    private readonly SugarRepository<RptSql> _thisRepository;
    private readonly SugarRepository<RptSqlColumn> _sqlColumnRepository;
    private readonly RptDatabaseService _databaseService;
    private readonly RptSqlColumnService _reportSqlColumnService;

    /// <summary>
    /// 
    /// </summary>
    /// <param name="thisRepository"></param>
    /// <param name="reportSqlColumnService"></param>
    /// <param name="databaseService"></param>
    /// <param name="sqlColumnRepository"></param>
    public RptSqlService(SugarRepository<RptSql> thisRepository, RptSqlColumnService reportSqlColumnService, RptDatabaseService databaseService, SugarRepository<RptSqlColumn> sqlColumnRepository)
    {
        _thisRepository = thisRepository;
        _reportSqlColumnService = reportSqlColumnService;
        _sqlColumnRepository = sqlColumnRepository;
        _databaseService = databaseService;
    }

    /// <summary>
    /// 查询所有/分页
    /// </summary>
    /// <param name="data"></param>
    /// <returns></returns> 
    public async Task<RestfulResult<PageResult<RptSql>>> GetPagesAsync(RptSqlParam? data)
    {
        var restful = new RestfulResult<PageResult<RptSql>>() { Code = CodeStatus.OK };
        data ??= new RptSqlParam { PageNo = 1, PageSize = 10 };
        var query = await _thisRepository.AsQueryable()
        .WhereIF(!string.IsNullOrEmpty(data.SqlName), it => it.SqlName.Contains(data.SqlName))
        .WhereIF(data.DatabaseId != null, it => it.DatabaseId == data.DatabaseId)
            .ToPageAsync(data.PageNo, data.PageSize);

        restful.Data = query;
        return restful;
    }

    /// <summary>
    /// 获取数据
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    public RptSql GetDataById(long id)
    {
        return _thisRepository.GetById(id);
    }


    /// <summary>
    /// 解析 SQL(SQL)
    /// </summary>
    /// <returns></returns>
    public RestfulResult PostParseSQLText([FromBody] RptSql parm)
    {
        RptDatabase dbConfig = _databaseService.GetDataById(parm.DatabaseId);
        using (var db = DbProvider.GetSugarDbContextBySelected(dbConfig.ConnectionString, dbConfig.DataType ?? 0))
        {
            return Restful.Success(_reportSqlColumnService.GetReportMetaDataColumns(db, parm.QuerySql));
        }
    }

    /// <summary>
    /// 所有
    /// </summary>
    /// <returns></returns>
    public async Task<RestfulResult<PageResult<RptSql>>> GetlistAsync()
    {
        var restful = new RestfulResult<PageResult<RptSql>>() { Code = CodeStatus.OK };
        RptSqlParam data = new RptSqlParam { PageNo = 1, PageSize = 100 };
        var query = await _thisRepository.AsQueryable()
        .WhereIF(!string.IsNullOrEmpty(data.SqlName), it => it.SqlName.Contains(data.SqlName))
        .WhereIF(data.DatabaseId != null, it => it.DatabaseId == data.DatabaseId)

            .ToPageAsync(data.PageNo, data.PageSize);
        restful.Data = query;
        return restful;
    }

    /// <summary>
    /// 增加
    /// </summary>
    /// <param name="data"></param>
    /// <returns></returns>
    [UnitOfWork]
    public async Task<RestfulResult> AddAsync(SqlDto data)
    {
        if (data == null)
            return Restful.Bad(CodeStatus.BadRequest, i18n.upload_data_required);
        var isExist = await _thisRepository.IsAnyAsync(u => u.SqlName == data.ReportSql.SqlName || u.Code == data.ReportSql.Code);
        if (isExist)
            return Restful.Bad(CodeStatus.BadRequest, i18n.data_duplicate);

        var result = await _thisRepository.Context.Ado.UseTranAsync(async () =>
        {
            List<RptSqlColumn> reportSqlColumn = data.ReportSqlField;
            if (reportSqlColumn == null || reportSqlColumn.Count == 0)
            {
                throw new ParamiterException(i18n.QueryColumnCannotBeEmpty);
            }
            RptSql rptSql = data.ReportSql.Adapt<RptSql>();

            if (rptSql.DatabaseId <= 0) { throw new ParamiterException(i18n.DataSourceCannotBeEmpty); }
            if (rptSql.QuerySql.IsNullOrWhiteSpace()) { throw new ParamiterException(i18n.SqlCannotBeEmpty); }

            rptSql.IsDelete = 0;
            var ret = await _thisRepository.InsertReturnEntityAsync(rptSql);
            if (ret != null)
            {
                List<RptSqlColumn> rptSqlColumns = new();
                foreach (var item in reportSqlColumn)
                {
                    item.SqlId = rptSql.Id;
                    var column = item.Adapt<RptSqlColumn>();
                    column.Id = IdGenerator.Id;
                    if (!string.IsNullOrWhiteSpace(column.SqlField))
                    {
                        //column.ParentId = null; //先不指定父级ID
                        if (!string.IsNullOrWhiteSpace(column.DictType))
                        {
                            column.HtmlType = string.IsNullOrWhiteSpace(column.DictType) ? "select" : column.HtmlType;
                            column.HtmlTypeQuery = column.HtmlType;
                        }
                        rptSqlColumns.Add(column);
                    }
                }
                await _sqlColumnRepository.InsertRangeAsync(rptSqlColumns);
            }
        });
        return result.Data == true ? Restful.Success(true) : Restful.Bad(CodeStatus.BadRequest, result.ErrorMessage);
    }

    /// <summary>
    /// 更新
    /// </summary>
    /// <param name="data"></param>
    /// <returns></returns>
    public async Task<RestfulResult> UpdateAsync(SqlDto data)
    {
        if (data == null)
            return Restful.Bad(CodeStatus.BadRequest, i18n.upload_data_required);

        var isExist = await _thisRepository.IsAnyAsync(u => u.Id == data.ReportSql.Id);
        if (!isExist)
            return Restful.Bad(CodeStatus.BadRequest, i18n.not_exist);

        isExist = await _thisRepository.IsAnyAsync(u => (u.SqlName == data.ReportSql.SqlName || u.Code == data.ReportSql.Code) && u.Id != data.ReportSql.Id);
        if (isExist)
            return Restful.Bad(CodeStatus.BadRequest, i18n.data_duplicate);

        var result = await _thisRepository.Context.Ado.UseTranAsync(async () =>
        {
            List<RptSqlColumn> reportSqlColumn = data.ReportSqlField;
            if (reportSqlColumn == null || reportSqlColumn.Count == 0)
            {
                throw new ParamiterException(i18n.QueryColumnCannotBeEmpty);
            }

            RptSql rptSql = data.ReportSql.Adapt<RptSql>();

            if (rptSql.DatabaseId <= 0) { throw new ParamiterException(i18n.DataSourceCannotBeEmpty); }
            if (rptSql.QuerySql.IsNullOrWhiteSpace()) { throw new ParamiterException(i18n.SqlCannotBeEmpty); }

            rptSql.IsDelete = 0;
            List<RptSqlColumn> rptSqlColumns = new();
            foreach (var item in reportSqlColumn)
            {
                item.SqlId = rptSql.Id;
                var column = item.Adapt<RptSqlColumn>();
                column.Id = IdGenerator.Id;
                if (!string.IsNullOrWhiteSpace(column.SqlField))
                {
                    //column.ParentId = null; //先不指定父级ID
                    if (!string.IsNullOrWhiteSpace(column.DictType))
                    {
                        column.HtmlType = string.IsNullOrWhiteSpace(column.DictType) ? "select" : column.HtmlType;
                    }
                    rptSqlColumns.Add(column);
                }
            }
            int count = await _thisRepository.AsUpdateable(rptSql).IgnoreColumns().ExecuteCommandAsync(); ;
            if (count == 1)
            {
                // 删除旧数据
                await _sqlColumnRepository.AsDeleteable().Where(x => x.SqlId == rptSql.Id).ExecuteCommandAsync();
                await _sqlColumnRepository.InsertRangeAsync(rptSqlColumns);
            }
        });

        return result.Data == true ? Restful.Success(true) : Restful.Bad(CodeStatus.BadRequest, result.ErrorMessage);
    }

    /// <summary>
    /// 删除
    /// </summary>
    /// <param name="data"></param>
    /// <returns></returns> 
    public async Task<RestfulResult> DeleteAsync([FromBody] EntityBaseId data)
    {
        RestfulResult restful = new() { Code = CodeStatus.BadRequest };

        if (data.Id == 0)
        {
            restful.Message = $"{i18n.delete}{i18n.fail}";
            return restful;
        }
        var result = await _thisRepository.Context.Ado.UseTranAsync(async () =>
        {
            bool isDelete = await _thisRepository.DeleteByIdAsync(data.Id);
            await _sqlColumnRepository.AsDeleteable().Where(x => x.SqlId == data.Id).ExecuteCommandAsync();

        });
        return result.Data == true ? Restful.Success($"{i18n.delete}{i18n.success}") : Restful.Bad(CodeStatus.BadRequest, $"{i18n.delete}{i18n.fail}");
    }

    /// <summary>
    /// 获取详情
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    public RestfulResult<SqlDto> GetDetail(long id)
    {
        RestfulResult<SqlDto> restfulResult = new();
        SqlDto sqlDto = new();
        sqlDto.ReportSql = _thisRepository.GetById(id);
        sqlDto.ReportSqlField = _reportSqlColumnService.GetReportSqlColumn(id);
        restfulResult.Data = sqlDto;
        return restfulResult;
    }


    /// <summary>
    /// 返回查询/报表数据、字典级联关系定义
    /// </summary>
    /// <param name="id">报表ID</param>
    /// <param name="parm">查询参数</param>
    /// <returns></returns>
    public async Task<RestfulResult<PageResultDto>> GetListAsync(long id, Dictionary<string, string> parm)
    {
        try
        {
            // 对数组型的参数进行处理 - 数组型的参数, 前端会在参数名称后增加"[]"
            parm = parm.ReplaceInKeys("[]", "");

            PageParam pageParam = new();

            pageParam.Sord = parm.GetValueOrDefault("sord") ?? string.Empty;
            pageParam.Sidx = parm.GetValueOrDefault("sidx") ?? string.Empty;

            pageParam.PageNo = int.Parse(parm.GetValueOrDefault("pageNo") ?? "0");
            pageParam.PageSize = int.Parse(parm.GetValueOrDefault("pageSize") ?? "10");

            if (pageParam.PageNo <= 0)
                pageParam.PageNo = 1;

            if (pageParam.PageSize <= 0)
                pageParam.PageSize = 10;

            // 1. 获取报表SQL定义
            RptSql reportSql = GetDataById(id);
            RptDatabase dbConfig = _databaseService.GetDataById(reportSql.DatabaseId);
            List<RptSqlColumn> reportSqlColumn = _reportSqlColumnService.GetReportSqlColumn(reportSql.Id);

            PageResultDto pageResult = new(pageParam.PageNo, pageParam.PageSize);

            // 2. 格式化查询条件
            if (dbConfig.DataType <= 0)
            {
                throw new ParamiterException(i18n.SpecifyDbEngine);
            }

            using (var db = DbProvider.GetSugarDbContextBySelected(dbConfig.ConnectionString, dbConfig.DataType ?? 0))
            {
                // 构建查询条件
                ISugarQueryable<object> queryable = db.SqlQueryable<object>(reportSql.QuerySql);

                // 添加查询条件
                RemoveKeys(ref parm);
                if (parm.Count > 0)
                {
                    List<RptSqlColumn> queryFields = reportSqlColumn.Where(it => it.IsQuery == YesOrNot.Y).ToList();

                    foreach (var column in queryFields)
                    {
                        string value = parm.GetValueOrDefault(column.SqlField);
                        if (!string.IsNullOrWhiteSpace(value))
                        {
                            switch (column.QueryType)
                            {
                                case "LIKE":
                                    queryable.Where($"{column.SqlField} LIKE @value", new { value = $"%{value}%" });
                                    break;
                                case "EQ":
                                    queryable.Where($"{column.SqlField} = @value", new { value });
                                    break;
                                case "BETWEEN":
                                    string secValue = parm.GetValueOrDefault(column.SqlField + "SecValue", "");
                                    if (!string.IsNullOrWhiteSpace(secValue))
                                    {
                                        queryable.Where($"{column.SqlField} BETWEEN @value1 AND @value2",
                                            new { value1 = value, value2 = secValue });
                                    }
                                    break;
                            }
                        }
                    }
                }

                // 使用 SqlSugar 原生分页查询
                RefAsync<int> totalNumber = 0;
                var data = await queryable.ToPageListAsync(pageParam.PageNo, pageParam.PageSize, totalNumber);

                // 转换为前端友好的格式，避免 DataTable 序列化问题
                if (data != null && data.Count > 0)
                {
                    var firstRow = data[0] as IDictionary<string, object>;
                    if (firstRow != null)
                    {
                        // 转换行数据
                        var rows = new List<Dictionary<string, object>>();
                        foreach (var row in data)
                        {
                            var dictRow = row as IDictionary<string, object>;
                            var convertedRow = new Dictionary<string, object>();

                            foreach (var col in dictRow)
                            {
                                var key = col.Key.FirstLower();
                                // 处理 DBNull 和日期格式
                                if (col.Value == DBNull.Value)
                                {
                                    convertedRow[key] = null;
                                }
                                else if (col.Value is DateTime dateTime)
                                {
                                    convertedRow[key] = dateTime.ToString("yyyy-MM-dd HH:mm:ss");
                                }
                                else
                                {
                                    convertedRow[key] = col.Value;
                                }
                            }
                            rows.Add(convertedRow);
                        }
                        pageResult.Rows = rows;
                    }
                }
                else
                {
                    pageResult.Rows = new List<Dictionary<string, object>>();
                }

                pageResult.TotalRows = totalNumber;
                return Restful<PageResultDto>.Success(pageResult);
            }
        }
        catch (Exception ex)
        {
            throw new ParamiterException($"{i18n.QueryException}：{ex.Message}");
        }
    }
    /// <summary>
    /// 移除无关参数
    /// </summary>
    /// <param name="parm"></param>
    private void RemoveKeys(ref Dictionary<string, string> parm)
    {
        parm.Remove("pageNo");
        parm.Remove("pageSize");
        parm.Remove("__preview");
    }

    /// <summary>
    /// 导出
    /// </summary>
    /// <param name="id"></param>
    /// <param name="parm"></param>
    /// <returns></returns>
    /// <exception cref="ParamiterException"></exception>
    public async Task<DataTable> ExportAsync(long id, [ModelBinder(BinderType = typeof(DictionaryBinder))] Dictionary<string, string> parm)
    {
        parm["pageNo"] = "1";
        parm["pageSize"] = ushort.MaxValue.ToString();

        var list = await GetListAsync(id, parm);
        List<RptSqlColumn> reportSqlColumn = _reportSqlColumnService.GetReportSqlColumn(id);

        // 只保留 IsExport = "1" 的列
        var exportColumns = reportSqlColumn.Where(item => item.IsExport == YesOrNot.Y).ToList();
        if (list.Data?.Rows == null || !(list.Data.Rows is List<Dictionary<string, object>> originalRows) || originalRows.Count == 0)
        {
            throw new ParamiterException(i18n.NoDataToExport);
        }

        // 创建新的DataTable
        DataTable exportTable = new DataTable();
        var columnMappings = new Dictionary<string, string>(); // 源列名 -> 目标列名映射

        // 添加要导出的列到新DataTable
        foreach (var col in exportColumns)
        {
            string finalColumnName = col.ColumnName;
            int suffix = 1;

            // 确保列名唯一
            while (exportTable.Columns.Contains(finalColumnName))
            {
                finalColumnName = $"{col.ColumnName}_{suffix}";
                suffix++;
            }

            // 添加列到新DataTable
            exportTable.Columns.Add(finalColumnName, typeof(string));
            columnMappings[col.SqlField] = finalColumnName;
        }
        // 复制数据
        foreach (var row in originalRows)
        {
            DataRow newRow = exportTable.NewRow();

            foreach (var mapping in columnMappings)
            {
                string sourceColumn = mapping.Key;
                string targetColumn = mapping.Value;

                if (row.ContainsKey(sourceColumn))
                {
                    var value = row[sourceColumn];
                    newRow[targetColumn] = FormatValueForExport(value);
                }
            }

            exportTable.Rows.Add(newRow);
        }

        return exportTable;

    }
    /// <summary>
    /// 格式化导出值
    /// </summary>
    /// <param name="value">原始值</param>
    /// <returns>格式化后的字符串</returns>
    private string FormatValueForExport(object value)
    {
        if (value == null)
            return string.Empty;

        return value switch
        {
            DateTime dateTime => dateTime.ToString("yyyy-MM-dd HH:mm:ss"),
            bool boolValue => boolValue ? "是" : "否",
            _ => value.ToString()
        };
    }
}